﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace newTurtle8
{
    public partial class QueryCard : UserControl
    {
        private QueryTabPage queryTabPage;
        static private string [] personalDataArr = {"יום הולדת","כתובת","דרכי התקשרות"};
        static private string [] armyDataArr = {"נעליים","הסמכות","תפקיד"};
        static private string [] HRDataArr = {"הכל","פעילים","לא פעילים", "מחלקה 1","מחלקה 2","מחלקה 3","מפלג","מפקדים"};
        static private string [] mofaDataArr = {"לקחים"};
        static private string [] mofaTypeDataArr = { "תעסוקה", "אימון", "הכל" };
        static private string [] lekachTypeDataArr = {"מבצעי","מנהלתי","בטיחותי","אחר","הכל"};
        static private string [] otherDataArr = { "איש קשר" };
        private string tabHeaderText = string.Empty;

        private const int MAX_TABS = 7;

        public QueryCard()
        {
            InitializeComponent();
            this.Dock = DockStyle.Fill;
        }

        private void insertTab()
        {
            SqlCommand selectCmd = SqlManager.SqlCommandFactory("SELECT [cols] FROM [Tables]");

            switch (comboBox1.Text)
            {
                case "מידע אישי":
                    handlePersonalData(selectCmd);
                    break;
                case "מידע צבאי":
                    handleArmyInfo(selectCmd);
                    break;
                case "כח-אדם":
                    handleHR(selectCmd);
                    break;
                case "מופעים":
                    handleMofa(selectCmd);
                    break;
                case "אחר":
                    handleOther(selectCmd);
                    break;
            }
            queryTabPage = new QueryTabPage(tabHeaderText, selectCmd);

            tabControl.TabPages.Insert(0, queryTabPage);
            tabControl.SelectedTab = queryTabPage;

            setDGVColumnAutoSizeMode();
            tabHeaderText = string.Empty;
        }

        private void toolStripButtonClear_Click(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();
            comboBox3.Items.Clear();
            comboBox4.Items.Clear();
            comboBox2.Visible = false;
            comboBox3.Visible = false;
            comboBox4.Visible = false;
            tabHeaderText = string.Empty;
        }


        private void handlePersonalData(SqlCommand selectCmd)
        {
            List<string> paramList = new List<string>();
            string tableName = "PersonalData";
            paramList.AddRange(new string[] { "PersonalData.firstName", "PersonalData.lastName", "PersonalData.armyID"});

            switch (comboBox2.Text)
            {
               
                case "דרכי התקשרות":
                    paramList.AddRange(new string[]{"PersonalData.email", "PersonalData.homePhone", "PersonalData.cellPhone"});     
                    replaceParameters(selectCmd, paramList, tableName);
                    break;

                case "כתובת":
                    paramList.AddRange(new string[]{"PersonalData.city","PersonalData.street","PersonalData.buildingNum","PersonalData.apartmentNum"});
                    replaceParameters(selectCmd, paramList, tableName);
                    break;

                default:
                    paramList.Add("PersonalData." + Translator.translateHebrewWord(comboBox2.Text));
                    replaceParameters(selectCmd, paramList, tableName);
                    break;
            }

            tabHeaderText = comboBox2.Text;
        }
        private void handleArmyInfo(SqlCommand selectCmd)
        {
            List<string> paramList = new List<string>();
            string tableName = "ArmyInfo INNER JOIN PersonalData ON ArmyInfo.armyID = PersonalData.armyID";
            paramList.AddRange(new string[] { "PersonalData.firstName", "PersonalData.lastName", "PersonalData.armyID" });

            switch (comboBox2.Text)
            {
                case "תפקיד":
                    paramList.Add("ArmyInfo.position");
                    replaceParameters(selectCmd, paramList, tableName);
                    break;

                case "הסמכות":
                    paramList.Add("ArmyInfo.qualifications");
                    replaceParameters(selectCmd, paramList, tableName);
                    break;

                case "נעליים":
                    paramList.AddRange(new string[]
                                        {"ArmyInfo.shoeSize",
                                        "ArmyInfo.shoeStatus",
                                        "ArmyInfo.noShoeComment",
                                        "ArmyInfo.idDiscStatus"});

                    replaceParameters(selectCmd, paramList, tableName);
                    break;
            }

            tabHeaderText = comboBox2.Text;

        }


        private void handleHR(SqlCommand selectCmd)
        {
            List<string> colsParamList = new List<string>();

            string tableName = "ArmyInfo INNER JOIN PersonalData ON ArmyInfo.armyID = PersonalData.armyID";
            colsParamList.AddRange(new string[] 
              { "PersonalData.firstName",
                "PersonalData.lastName", 
                "PersonalData.armyID",
                "ArmyInfo.platoon",
                "ArmyInfo.position"});

            switch (comboBox2.Text)
            {
                case "הכל":
                    replaceParameters(selectCmd, colsParamList , tableName);
                    break;

                case "פעילים":
                    replaceParameters(selectCmd, colsParamList, tableName, "ArmyInfo.isActive=@isActive");
                    selectCmd.Parameters.Add("@isActive", SqlDbType.Bit).Value = true;
                    break;

                case "לא פעילים":
                    replaceParameters(selectCmd, colsParamList, tableName, "ArmyInfo.isActive=@isActive");
                    selectCmd.Parameters.Add("@isActive", SqlDbType.Bit).Value = false;
                    break;

                case "מחלקה 1":
                    replaceParameters(selectCmd, colsParamList, tableName, "ArmyInfo.platoon=1");                    
                    break;

                case "מחלקה 2":
                    replaceParameters(selectCmd, colsParamList, tableName, "ArmyInfo.platoon=2");
                    break;

                case "מחלקה 3":
                    replaceParameters(selectCmd, colsParamList, tableName, "ArmyInfo.platoon=3");
                    break;

                case "מפלג":
                    replaceParameters(selectCmd, colsParamList, tableName, "ArmyInfo.platoon=4");
                    break;

                case "מפקדים":
                    selectCmd.CommandText = SqlManager.getSqlStringFromFile(@"\SELECT\CompanyPakalPanel.SELECT.commander.sql");
                    break;
            }

            tabHeaderText = comboBox2.Text;
        }

        private void handleMofa(SqlCommand selectCmd)
        {
            selectCmd.CommandText = "SELECT startDate, mofaType , lekachType, lekachData FROM Lekachim";
            switch (comboBox3.Text)
	        {
                case "לקחים":
                    if (!comboBox2.Text.Equals("הכל"))
                    {
                        selectCmd.CommandText += " WHERE mofaType=@mofaType";
                        selectCmd.Parameters.Add("@mofaType", SqlDbType.NVarChar).Value = comboBox2.Text;
                    }
                    if (!comboBox4.Text.Equals("הכל"))
                    {
                        selectCmd.CommandText += " AND lekachType=@lekachType";
                        selectCmd.Parameters.Add("@lekachType", SqlDbType.NVarChar).Value = comboBox4.Text;
                    }

                    break;

	    	    default:
                break;
	        }

            tabHeaderText = comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
        }

        private void handleOther(SqlCommand selectCmd)
        {
            List<string> paramList = new List<string>();
            string tableName;
            
             
            switch (comboBox2.Text)
            {
                case "איש קשר":
                       paramList.AddRange(new string[] 
                                                { "PersonalData.firstName",
                                                  "PersonalData.lastName", 
                                                  "PersonalData.armyID",
                                                  "ArmyInfo.platoon",
                                                  "ContactInfo.firstName AS contactFirstName",
                                                  "ContactInfo.lastName AS contactLastName",
                                                  "ContactInfo.email",
                                                  "ContactInfo.homePhone",
                                                  "ContactInfo.cellPhone"});
                       tableName = "ContactInfo INNER JOIN PersonalData ON ContactInfo.armyID = PersonalData.armyID INNER JOIN ArmyInfo ON ArmyInfo.armyID = PersonalData.armyID";
                       replaceParameters(selectCmd, paramList, tableName);
                      break;
            }
        }

        private void buttonGo_Click(object sender, EventArgs e)
        {
            if (tabControl.TabCount < MAX_TABS)
                insertTab();
            else
            {
                MessageBox.Show("יש למחוק שאילתות ישנות על מנת להמשיך",
                                "מכסה מלאה",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk,
                                MessageBoxDefaultButton.Button1
                                );
            }
        }

        private void toolStripButtonDelete_Click(object sender, EventArgs e)
        {
            if (tabControl.TabPages.Count == 0)
                return;
            DialogResult result = MessageBox.Show("האם אתה בטוח שברצונך למחוק את כל השאילתות",
                                                  "וידוא מחיקה",
                                                  MessageBoxButtons.OKCancel,
                                                  MessageBoxIcon.Asterisk,
                                                  MessageBoxDefaultButton.Button1
                                                  );
            if (result == DialogResult.OK)
            {
                tabControl.TabPages.Clear();
                tabHeaderText = string.Empty;
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            buttonGo.Enabled = false;
            
            string item = (string)comboBox1.SelectedItem;
            comboBox2.Items.Clear();
            switch (item)
            {
                case "מידע אישי":
                    comboBox2.Items.AddRange(personalDataArr);
                    break;
                case "כח-אדם":
                    comboBox2.Items.AddRange(HRDataArr);
                    break;
                case "מידע צבאי":
                    comboBox2.Items.AddRange(armyDataArr);
                    break;
                case "מופעים":
                    comboBox2.Items.AddRange(mofaTypeDataArr);
                    break;
                case "אחר":
                    comboBox2.Items.AddRange(otherDataArr);
                    break;

                default:
                    break;
            }
            comboBox2.Visible = true;
            comboBox3.Visible = false;
            comboBox4.Visible = false;

            comboBox2.Text = "בחר...";
            comboBox3.Text = "בחר...";
            comboBox4.Text = "בחר...";
        }

        private void tabControl_MouseClick(object sender, MouseEventArgs e)
        {
            Point p = e.Location;
            Rectangle rect = this.tabControl.GetTabRect(tabControl.SelectedIndex);
            rect.Offset(5, 0);
            rect.Width = 16;
            rect.Height = 16;
            if (rect.Contains(p))
            {
                TabPage TabP = tabControl.TabPages[tabControl.SelectedIndex];
                tabControl.TabPages.Remove(TabP);
            }
        }

        private void toolStripButtonPrint_Click(object sender, EventArgs e)
        {
            DataGridView dgv = new DataGridView();
            queryTabPage = (QueryTabPage)tabControl.SelectedTab;
            dgv = queryTabPage.DGV;
            if (dgv.RowCount < 2)
                return;
            // Calling DataGridView Printing
            QueryPrintDGV.Print_DataGridView(dgv);
        }

        private void tabControl_Selecting(object sender, TabControlCancelEventArgs e)
        {
            foreach (TabPage tp in tabControl.TabPages)
                if (tp == tabControl.SelectedTab)
                    tp.ImageIndex = 0;
                else tp.ImageIndex = 1;
            
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!comboBox1.Text.Equals("מופעים"))
                buttonGo.Enabled = true;            
            else
            {
                comboBox3.Items.Clear();
                comboBox3.Visible = true;
                comboBox3.Items.AddRange(mofaDataArr);
            }
        }

        private SqlCommand replaceParameters(SqlCommand cmd, List<string> paramList, string tableName)
        {
            string colsStr = string.Empty;
            for (int i = 0; i < paramList.Count; i++)
                colsStr += paramList[i] + ",";
                
            cmd.CommandText = cmd.CommandText.Replace("[cols]", colsStr.Substring(0,colsStr.Length - 1));
            cmd.CommandText = cmd.CommandText.Replace("[Tables]", tableName);

            return cmd;
        }
        private SqlCommand replaceParameters(SqlCommand cmd, List<string> paramList, string tableName, string conds)
        {
            replaceParameters(cmd, paramList, tableName);
            cmd.CommandText += " WHERE [conds]";
            cmd.CommandText = cmd.CommandText.Replace("[conds]", conds);

            return cmd;
        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox4.Items.Clear();
            comboBox4.Items.AddRange(lekachTypeDataArr);
            if (comboBox2.Text.Equals("אימון"))
                comboBox4.Items.Remove("מבצעי");
            comboBox4.Visible = true;
        }

        private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
        {
            buttonGo.Enabled = true;
        }

        private void setDGVColumnAutoSizeMode()
        {
            if (comboBox3.Text.Equals("לקחים"))
            {
                QueryTabPage qtp = (QueryTabPage)tabControl.TabPages[tabControl.TabPages.Count - 1];
                qtp.DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                qtp.DGV.Columns[qtp.DGV.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            }
        }
        
    }
}
